﻿//-------------------------------------------------------------------
//文件名称：TQuestion.cs
//模块名称：TQuestion数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.Question;

namespace ELearning.DAL
{
    /// <summary>
    /// TQuestion数据访问层
    /// </summary>
    public partial class TQuestionRepository : BaseRepository<TQuestion>
    {
        /// <summary>
        /// 获取题目列表
        /// </summary>
        public List<QuestionModel> GetModelList(TQuestionRequest request)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;

            if (!string.IsNullOrEmpty(request.MainName))
                where = $" AND q.MainName LIKE N'%{request.MainName}%'";

            if (!string.IsNullOrEmpty(request.Name))
                where = $"  {where} AND q.Name LIKE N'%{request.Name}%'";

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND q.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND q.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            var sql = $@"
                
        SELECT
			*
		FROM
			(
				SELECT
					u.UserName,
					q.*,
					d.TypeName CategoryName,
					dd.TypeName,
					qt.TypeName QuestionTypeName,
					ROW_NUMBER() OVER(ORDER BY q.CreateTime DESC) rn,
					COUNT(1) OVER() TotalCount
				FROM
					dbo.TQuestion q
					LEFT JOIN dbo.EDictionary d ON q.CategoryId = d.TypeCode AND d.IsDelete = 0
					LEFT JOIN dbo.EDictionary dd ON q.TypeCode = dd.TypeCode AND dd.IsDelete = 0
					LEFT JOIN dbo.ESysUser u ON u.UserID = q.CreateUserId AND u.IsDelete = 0
					LEFT JOIN dbo.TQuestionType qt ON qt.TypeCode = q.QuestionTypeCode AND qt.IsDelete = 0
				WHERE
					q.IsDelete = 0 {where}
			)a
		WHERE
			a.rn BETWEEN {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<QuestionModel>(sql).ToList();
        }
    }
}


